package cn.dao;

import cn.pojo.Announcement;
import cn.util.DaoCount;
import cn.util.DaoUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @author jia
 * @Description
 * @date 2019-03-21-10:17
 */
public class AnnouncementDao {
    /**
     * 添加一个公告
     */
    public void add(Announcement announcement) {
        String sql = "INSERT INTO announcement(aTitle, aText, aDate) VALUES (?, ?, NOW())";
        try (Connection connection = DaoUtil.getConnection();
             PreparedStatement ps = connection.prepareStatement(sql)) {
            ps.setString(1, announcement.getaTitle());
            ps.setString(2, announcement.getaText());
            ps.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 删除一个公告
     */
    public void delete(int id) {
        String sql = "delete from announcement where id = ?";
        try (Connection connection = DaoUtil.getConnection();
            PreparedStatement ps = connection.prepareStatement(sql)) {
            ps.setInt(1, id);
            ps.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 修改公告
     */
    public void update(Announcement announcement) {
        String sql = "update announcement set aTitle = ?, aText = ?, aDate = NOW() where id = ?";
        try (Connection connection = DaoUtil.getConnection();
             PreparedStatement ps = connection.prepareStatement(sql)) {
            ps.setString(1, announcement.getaTitle());
            ps.setString(1, announcement.getaText());
            ps.setInt(1, announcement.getId());
            ps.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 查询所有公告
     */
    public List<Announcement> getList() {
        List<Announcement> announcementList = new ArrayList<>();
        String sql = "select * from announcement";
        try (Connection connection = DaoUtil.getConnection();
             Statement statement = connection.createStatement()) {
            ResultSet rs = statement.executeQuery(sql);
            while (rs.next()) {
                Announcement announcement = new Announcement();
                announcement.setId(rs.getInt("id"));
                announcement.setaTitle(rs.getString("aTitle"));
                announcement.setaText(rs.getString("aText"));
                announcement.setaDate(rs.getDate("aDate"));
                announcementList.add(announcement);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return announcementList;
    }

    /**
     * 查询总记录数
     */
    public int getTotal() {
        int total = 0;
        String sql = "select count(id) from announcement";
        return DaoCount.count(sql);
    }
}
